package com.css.fxfzypg.modules.model.repository.Impl;

import com.css.fxfzypg.constants.YNEnum;
import com.css.fxfzypg.modules.model.entity.Material;
import com.css.fxfzypg.modules.model.repository.MaterialRepository;
import com.css.fxfzypg.modules.model.vo.MaterialVO;
import com.css.fxfzypg.util.CreateGson;
import com.css.fxfzypg.util.PlatformObjectUtils;
import com.css.fxfzypg.web.PlatformSessionContext;
import com.css.fxfzypg.zcpt.sys.entity.SUser;
import com.css.fxfzypg.zcpt.sys.service.SUserService;
import com.css.fxfzypg.zcpt.sys.service.SysAreaService;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author Dbj
 * @date 2022年10月05日  17:37
 */
@Repository
public class MaterialRepositoryImpl implements MaterialRepository {

    @Resource
    private JdbcTemplate jdbcTemplate;

    @Resource
    private SUserService sUserService;

    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Resource
    private SysAreaService areaService;

    @Override
    public Map<String, Object> findPage(MaterialVO vo, int curPage, int pageSize) {
        StringBuilder sql = new StringBuilder("select * from material ");
        StringBuilder whereSql = new StringBuilder(" where is_valid ='" + YNEnum.Y.toString() + "'");
        String orderSql = " order by is_default desc,province_code";
        StringBuilder countSql = new StringBuilder("select count(*) from material ");
        StringBuilder str = new StringBuilder("");
        String userId = PlatformSessionContext.getUserID();
        String user = sUserService.getSUser(PlatformSessionContext.getUserID());
        SUser sUser = CreateGson.createGson().fromJson(user, SUser.class);
        if (!vo.getNation()) {
            whereSql.append(" and (org_name = '系统默认' or province='").append(sUser.getProvinceId()).append("')");
        }
        if (!PlatformObjectUtils.isEmpty(vo.getModelName())) {
            whereSql.append(" and model_name like '%" + vo.getModelName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(vo.getProvince())) {
            if ("cd726776c7de41e4a41dcdb3d90b2806".equals(vo.getProvince()) || "225ac763ab714524b87d95140a93a4a5".equals(vo.getProvince()) || "8a3f7d3f78ad456f92f487a979091c7f".equals(vo.getProvince()) || "afcbf07008a34c2e85917cfd34008033".equals(vo.getProvince())) {
                //拿到北京上海天津重庆四省的省id
                String pid = areaService.getParentId(vo.getProvince());
                vo.setProvince(pid);
            }
            whereSql.append(" and province='").append(vo.getProvince()).append("'");
        }
        if (!PlatformObjectUtils.isEmpty(vo.getCity())) {
            whereSql.append(" and (position('").append(vo.getCity()).append("' in city) > 0 ");
            whereSql.append(" or city='全部')");
        }
        String limitSql = " limit " + pageSize + " offset " + (curPage - 1) * pageSize;
        String sqlWhole = sql.append(whereSql).append(orderSql).append(limitSql).toString();
        countSql.append(whereSql);
        List<Material> list = jdbcTemplate.query(sqlWhole, new BeanPropertyRowMapper<>(Material.class));
        Integer total = jdbcTemplate.queryForObject(countSql.toString(), Integer.class);
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("total", total);
        map.put("rows", list);
        return map;
    }

    @Override
    public Material getModelById(String id) {
        String sql = "select * from material where is_valid ='" + YNEnum.Y.toString() + "' and id='" + id + "'";
        return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(Material.class));
    }

    @Override
    public Material getDefaultModel() {
        String sql = "select * from material where is_valid ='" + YNEnum.Y.toString() +
                "' and is_default='" + YNEnum.Y.toString() + "' limit 1";
        return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(Material.class));
    }

    @Override
    public List<Material> getByDivision(String province, String city, int isNa) {
        String sql = "select * from Material where is_valid ='" + YNEnum.Y.toString() + "' and province='" + province + "'";
        if (PlatformObjectUtils.isNotEmpty(city)) {
            sql = sql + " and city='" + city + "'";
        }
        sql = sql + " order by create_time desc ";
        if (isNa == 0) {
            sql = sql + " limit 1 ";
        }
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Material.class));
    }

    @Override
    public void save(Material material) {
        String sql =
                "insert into material(id, model_name, province, city, create_user_id, create_user_name, create_time, "
                        + "org_id,org_name,is_valid,is_default,ratio1,ratio2,ratio6,ratio7,ratio8,ratio9,ratio10,"
                        + "ratio11,ratio12,ratio13,ratio14,ratio15,province_code)  values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        jdbcTemplate.update(sql, material.getId(), material.getModelName(), material.getProvince(), material.getCity(), material.getCreateUserId(), material.getCreateUserName(), material.getCreateTime(),
                material.getOrgId(), material.getOrgName(), material.getIsValid(), material.getIsDefault(), material.getRatio1(),
                material.getRatio2(), material.getRatio6(), material.getRatio7(), material.getRatio8(), material.getRatio9(), material.getRatio10(),
                material.getRatio11(), material.getRatio12(), material.getRatio13(), material.getRatio14(), material.getRatio15(), material.getProvinceCode());
    }

    @Override
    public void update(Material material) {
        String sql =
                "update material set model_name=?,ratio1=?,ratio2=?,ratio6=?,ratio7=?,ratio8=?,ratio9=?,ratio10=?,"
                        + "ratio11=?,ratio12=?,ratio13=?,ratio14=?,ratio15=?,update_user_id=?,update_time=? where id =?";
        jdbcTemplate.update(sql, material.getModelName(), material.getRatio1(), material.getRatio2(), material.getRatio6(), material.getRatio7(), material.getRatio8(), material.getRatio9(), material.getRatio10()
                , material.getRatio11(), material.getRatio12(), material.getRatio13(), material.getRatio14(), material.getRatio15(), material.getUpdateUserId(), material.getUpdateTime(), material.getId());
    }

    @Override
    public void delete(String userId, Date updateTime, String id) {
        jdbcTemplate.update("update material set is_valid=?,update_user_id=?,update_time=? where id=?", YNEnum.N.toString(), userId, updateTime, id);
    }

    @Override
    public Integer countByProvince(String province) {
        return jdbcTemplate.queryForObject("SELECT count(*) from material where province = '" + province + "' and is_valid ='1' ", Integer.class);
    }

    @Override
    public Integer countByYearPrefixLike(String taskYear, String province) {
        return jdbcTemplate.queryForObject("SELECT count(*) FROM material where (TO_CHAR(create_time, 'yyyy-MM-dd hh24:mi:ss') LIKE '" + taskYear + "%' or TO_CHAR(update_time, 'yyyy-MM-dd hh24:mi:ss') LIKE '" + taskYear + "%') and province = '" + province + "' and is_valid ='1' ", Integer.class);

    }
}
